/*
 Navicat Premium Data Transfer

 Source Server         : demo
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : localhost:3306
 Source Schema         : 0313-monthTest

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 01/09/2023 16:57:46
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for house
-- ----------------------------
DROP TABLE IF EXISTS `house`;
CREATE TABLE `house` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '楼盘名称',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '楼盘地址',
  `province_id` int DEFAULT NULL COMMENT '省id',
  `city_id` int DEFAULT NULL COMMENT '市id',
  `pic_url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片地址',
  `area` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '建筑面积',
  `sms` int DEFAULT NULL COMMENT '是否已发送短信  0否 1是',
  PRIMARY KEY (`id`),
  KEY `idx_region` (`province_id`,`city_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of house
-- ----------------------------
BEGIN;
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (1, '碧桂园小区', '和平路1号', 1, 4, NULL, '1000', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (2, '保利小区', '健康路1号', 2, 9, NULL, '2000', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (3, '融创小区', '胜利路1号', 3, 13, NULL, '3000', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (4, '绿地小区', '建国路1号', 3, 14, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (5, '绿地小区3区', '建国路1号', 3, 15, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (6, '绿地小区2区', '建国路1号', 3, 15, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (7, '绿地小区4区', '建国路1号', 3, 16, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (8, '绿地小区5区', '建国路1号', 3, 16, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (9, '绿地小区6区', '建国路1号', 3, 17, NULL, '3500', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (10, '万达小区1号', '晋城高平市', 3, 17, 'http://localhost:8080/static/1672124638925saber02.jpeg', '200', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (11, 'dddd', 'ddddd', 1, 4, 'http://localhost:8080/static/1672195230428saber02.jpeg', '200', NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (12, '但是风格的复古的', '豆腐干豆腐豆腐', 1, 4, 'http://localhost:8080/static/036fbb60-b11d-477a-baff-a6ceb0beca81kung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (13, 'SDFBGDFBDG', 'DGDGDGD', 1, 5, 'http://localhost:8080/static/c794a72f-9298-4dbb-a13e-0f7bf7e756e5kung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (14, 'cc hu', 'tianjin', 1, 4, 'http://localhost:8080/static/92040fa2-4c45-4751-b806-ec40b6e2559fkung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (15, 'cc hu', 'tianjin', 2, 9, 'http://localhost:8080/static/4e3b4358-70c9-4cae-9430-45016839c76dkung.png', NULL, NULL);
INSERT INTO `house` (`id`, `name`, `address`, `province_id`, `city_id`, `pic_url`, `area`, `sms`) VALUES (16, 'cc hu', 'tianjin', 3, 16, 'http://localhost:8080/static/6a8f2cc7-ce6c-4e45-95ba-4a0f9fcf8a61test01.jpg', NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for people_info
-- ----------------------------
DROP TABLE IF EXISTS `people_info`;
CREATE TABLE `people_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `post_id` int DEFAULT NULL,
  `source_id` int DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `result_id` int DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_post_source_status` (`post_id`,`source_id`,`status`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of people_info
-- ----------------------------
BEGIN;
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (1, 1, 1, '张三', '17611221735', 1, '面试中', '2023-08-27 10:53:34');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (2, 2, 2, '李四', '17611221735', 1, '面试中', '2023-08-27 10:53:34');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (3, 1, 1, '王五', '17611221735', 2, '已离职', '2023-08-27 10:53:34');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (4, 2, 3, 'admin', '17611221735', 3, '面试中', '2023-08-27 10:53:34');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (5, 3, 3, 'Jerry', '17611221735', 3, '已离职', '2023-08-28 10:53:50');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (6, 3, 2, 'Tom', '17611221735', 1, '面试中', '2023-08-28 10:53:50');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (7, 1, 3, 'a', '17611221735', 1, '面试中', '2023-08-28 10:53:50');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (8, 2, 3, 'b', '17611221735', 1, '已入职', '2023-08-28 10:53:50');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (9, 2, 3, 'c', '17611221735', 2, '已入职', '2023-08-28 10:53:50');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (10, 3, 3, 'd', '17611221735', 3, '已入职', '2023-08-28 10:53:50');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (11, 2, 1, 'e', '17611221735', 2, '已离职', '2023-08-28 10:53:50');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (12, 2, 1, 'f', '17611221735', 2, '已离职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (13, 1, 3, 'g', '17611221735', 2, '已离职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (14, 2, 2, 'x', '17611221735', 1, '面试中', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (15, 1, 1, 'z', '17611221735', 1, '已离职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (16, 2, 3, 'v', '17611221735', 1, '面试中', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (17, 3, 3, 'n', '17611221735', 1, '已离职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (18, 3, 2, 'm', '17611221735', 1, '面试中', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (19, 1, 3, 'j', '17611221735', 1, '面试中', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (20, 2, 3, 'u', '17611221735', 1, '已入职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (21, 2, 3, 'i', '17611221735', 1, '已入职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (22, 3, 3, 'o', '17611221735', 1, '已入职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (23, 2, 1, 'p', '17611221735', 1, '已离职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (24, 2, 1, 'k', '17611221735', 1, '已离职', '2023-08-23 10:53:57');
INSERT INTO `people_info` (`id`, `post_id`, `source_id`, `name`, `phone`, `result_id`, `status`, `create_time`) VALUES (25, 1, 3, 'l', '17611221735', 1, '已离职', '2023-08-23 10:53:57');
COMMIT;

-- ----------------------------
-- Table structure for people_post
-- ----------------------------
DROP TABLE IF EXISTS `people_post`;
CREATE TABLE `people_post` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of people_post
-- ----------------------------
BEGIN;
INSERT INTO `people_post` (`id`, `name`) VALUES (1, '开发');
INSERT INTO `people_post` (`id`, `name`) VALUES (2, '测试');
INSERT INTO `people_post` (`id`, `name`) VALUES (3, '运维');
COMMIT;

-- ----------------------------
-- Table structure for people_result
-- ----------------------------
DROP TABLE IF EXISTS `people_result`;
CREATE TABLE `people_result` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of people_result
-- ----------------------------
BEGIN;
INSERT INTO `people_result` (`id`, `name`) VALUES (1, '录用');
INSERT INTO `people_result` (`id`, `name`) VALUES (2, '不合格');
INSERT INTO `people_result` (`id`, `name`) VALUES (3, '沟通中');
COMMIT;

-- ----------------------------
-- Table structure for people_source
-- ----------------------------
DROP TABLE IF EXISTS `people_source`;
CREATE TABLE `people_source` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of people_source
-- ----------------------------
BEGIN;
INSERT INTO `people_source` (`id`, `name`) VALUES (1, '拉钩');
INSERT INTO `people_source` (`id`, `name`) VALUES (2, 'boss直聘');
INSERT INTO `people_source` (`id`, `name`) VALUES (3, '猎聘');
COMMIT;

-- ----------------------------
-- Table structure for region
-- ----------------------------
DROP TABLE IF EXISTS `region`;
CREATE TABLE `region` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
  `parent_id` int DEFAULT NULL COMMENT '父级id',
  `level` int DEFAULT NULL COMMENT '0 代表省,  1代表市',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of region
-- ----------------------------
BEGIN;
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (1, '河北省', NULL, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (2, '山东省', NULL, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (3, '山西省', NULL, 0);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (4, '廊坊市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (5, '邢台市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (6, '唐山市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (7, '石家庄市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (8, '邯郸市', 1, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (9, '济南市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (10, '潍坊市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (11, '青岛市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (12, '菏泽市', 2, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (13, '太原市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (14, '阳泉市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (15, '吕梁市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (16, '大同市', 3, 1);
INSERT INTO `region` (`id`, `name`, `parent_id`, `level`) VALUES (17, '晋城市', 3, 1);
COMMIT;

-- ----------------------------
-- Table structure for user_info
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `salt` varchar(255) DEFAULT NULL,
  `nick_name` varchar(255) DEFAULT NULL,
  `avatar_url` varchar(255) DEFAULT NULL,
  `gitee_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of user_info
-- ----------------------------
BEGIN;
INSERT INTO `user_info` (`id`, `name`, `password`, `salt`, `nick_name`, `avatar_url`, `gitee_id`) VALUES (9, 'admin', '46f94c8de14fb36680850768ff1b7f2a', 'qwe', NULL, NULL, NULL);
INSERT INTO `user_info` (`id`, `name`, `password`, `salt`, `nick_name`, `avatar_url`, `gitee_id`) VALUES (10, 'hccxx', NULL, NULL, 'Akun', 'https://foruda.gitee.com/avatar/1692953594567483110/12065236_hccxx_1692953594.png', 12065236);
COMMIT;

-- ----------------------------
-- Procedure structure for bing
-- ----------------------------
DROP PROCEDURE IF EXISTS `bing`;
delimiter ;;
CREATE PROCEDURE `bing`()
BEGIN
SELECT count(*)as value, pp.name as name 
from people_info  pi 
LEFT JOIN people_post pp on pi.post_id = pp.id

GROUP BY pi.post_id;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for bingWithParam
-- ----------------------------
DROP PROCEDURE IF EXISTS `bingWithParam`;
delimiter ;;
CREATE PROCEDURE `bingWithParam`(IN source int, IN stu varchar(50))
BEGIN
SELECT count(*)as value, pp.name as name 
from people_info  pi 
LEFT JOIN people_post pp on pi.post_id = pp.id
where pi.source_id = source or pi.`status` = stu
GROUP BY pi.post_id;
END
;;
delimiter ;






SET FOREIGN_KEY_CHECKS = 1;
